package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.SignalLampDto;
import com.hualude.roadsign.dto.SignalLampFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by pjn on 2017/7/22.
 */
public class SignalLampRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<SignalLampDto> getSignalLamp4DT(DTRequestDto dtRequestDto,
                                                String actorIds,
                                                Integer orgId,
                                                Boolean audit,
                                                String startRoad,
                                                String endRoad,
                                                Integer alarmBinding) throws Exception {
        String sql = "select sm.*, ur.UserName as signalLampCreatorName, " +
                "og1.OrgName as signalLampOrgName, og2.OrgName as signalLampMainOrgName,a.DeviceState " +
                "from (tb_signal_Lamp sm, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (sm.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = sm.SignalLampCreatorId " +
                "and og1.OrgId = sm.SignalLampOrgId and og2.OrgId = sm.SignalLampMainOrgId and " +
                " sm.SignalLampStartRoad like '%" + startRoad + "%' and sm.SignalLampEndRoad like '%" + endRoad + "%'" +
                " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and SignalLampIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and sm.SignalLampMainOrgId=" + orgId + " ";
        else
            sql += "and sm.SignalLampOrgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += "union select sm.*, ur.UserName as signalLampCreatorName, " +
                    "og1.OrgName as signalLampOrgName, og2.OrgName as signalLampMainOrgName,a.DeviceState " +
                    "from (tb_signal_Lamp sm, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (sm.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = sm.SignalLampCreatorId " +
                    "and og1.OrgId = sm.SignalLampOrgId and og2.OrgId = sm.SignalLampMainOrgId and " +
                    " sm.SignalLampStartRoad like '%" + endRoad + "%' and sm.SignalLampEndRoad like '%" + startRoad + "%'" +
                    " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and SignalLampIsComplete = 0 ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += "and sm.SignalLampOrgId = " + orgId + " ";
        }
        sql += "order by SignalLampInstallTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();

    }

    public Integer getSignalLamp4DTCount(DTRequestDto dtRequestDto,
                                         String actorIds,
                                         Integer orgId,
                                         Boolean audit,
                                         String startRoad,
                                         String endRoad,
                                         Integer alarmBinding) throws Exception {
        String sql = "select count(*) " +
                "from tb_signal_Lamp sm " +
                " where sm.SignalLampStartRoad like '%" + startRoad + "%' and sm.SignalLampEndRoad like '%" + endRoad + "%'" +
                " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (audit)
            sql += "and SignalLampIsComplete = 0 ";
        if (alarmBinding != null) {
            if (alarmBinding == 1)
                sql += " and AlarmDeviceId is not NULL ";
            else if (alarmBinding == 2)
                sql += " and AlarmDeviceId is NULL ";
        }
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and sm.SignalLampMainOrgId=" + orgId + " ";
        else
            sql += "and sm.SignalLampOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count1 = Integer.parseInt(query.getSingleResult().toString());
        Integer count2 = 0;
        if (startRoad != "" || endRoad != "") {
            sql = "select count(*) " +
                    "from tb_signal_Lamp sm " +
                    " where sm.SignalLampStartRoad like '%" + endRoad + "%' and sm.SignalLampEndRoad like '%" + startRoad + "%'" +
                    " and sm.ProjectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (audit)
                sql += "and SignalLampIsComplete = 0 ";
            if (alarmBinding != null) {
                if (alarmBinding == 1)
                    sql += " and AlarmDeviceId is not NULL ";
                else if (alarmBinding == 2)
                    sql += " and AlarmDeviceId is NULL ";
            }
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += "and sm.SignalLampOrgId = " + orgId + " ";
            query = em.createNativeQuery(sql);
            count2 = Integer.parseInt(query.getSingleResult().toString());
        }
        return count2 + count1;
    }


    public Integer getLampFaultReportCount4DT(String search, Integer actorId, Integer orgId, String signalLampStartRoad, String signalLampEndRoad) throws Exception {
        String sql = "select count(*) " +
                "FROM ((tb_signal_Lamp sm JOIN tb_org o ON o.OrgId=sm.SignalLampOrgId)\n" +
                "JOIN tb_user u ON u.UserId=sm.SignalLampCreatorId)\n" +
                "WHERE sm.SignalLampStartRoad LIKE '%" + signalLampStartRoad + "%' AND sm.SignalLampEndRoad LIKE '%" + signalLampEndRoad + "%'";
        if (actorId != 1) {
            sql += " and sm.SignalLampOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }


    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT sm.SignalLampStartRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " WHERE sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " WHERE sm.SignalLampOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalLampEndRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " WHERE sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " WHERE sm.SignalLampOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "SELECT sm.SignalLampStartRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm\n" +
                "WHERE sm.SignalLampEndRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalLampEndRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm\n" +
                "WHERE sm.SignalLampStartRoad LIKE '%" + roadName + "%'";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getStartRoadByCity(String city, Integer actorId, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.SignalLampStartRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm where sm.signalLampCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalLampEndRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm where  sm.signalLampCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();

    }

    public List<SignalLampDto> getSignalLampCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select signalLampId, signalLampLongitude as longitude ,signalLampLatitude as latitude from tb_signal_Lamp ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "where SignalLampOrgId=" + orgId + "";
            if (orgType == 1)
                sql += "where SignalLampMainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.getResultList();
    }

    public List<SignalLampDto> getSignalLamp4APP(String actorIds,
                                                 Integer orgId,
                                                 Integer mCount,
                                                 Integer mSize,
                                                 String city,
                                                 String startRoad,
                                                 String endRoad,
                                                 String alarmType) throws Exception {
        String sql = "select se.*, ur.UserName as signalLampCreatorName, " +
                "og1.OrgName as signalLampOrgName, og2.OrgName as signalLampMainOrgName,a.DeviceState " +
                "from (tb_signal_Lamp se, tb_user ur, tb_org og1, tb_org og2) " +
                "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                "where ur.UserId = se.signalLampCreatorId " +
                "and og1.OrgId = se.signalLampOrgId and og2.OrgId = se.signalLampMainOrgId " +
                "and se.signalLampStartRoad like '%" + startRoad + "%' and se.signalLampEndRoad like '%" + endRoad + "%' " +
                "and se.signalLampCity like '%" + city + "%'";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and se.signalLampMainOrgId=" + orgId + " ";
        else
            sql += "and se.signalLampOrgId = " + orgId + " ";
        if (alarmType.contains("0"))
            sql += " and se.AlarmDeviceId is NULL ";
        else if (alarmType.contains("1"))
            sql += " and se.AlarmDeviceId is not NULL ";
        else
            sql += "";
        if (startRoad != "" || endRoad != "") {
            sql += "union select se.*, ur.UserName as signalLampCreatorName, " +
                    "og1.OrgName as signalLampOrgName, og2.OrgName as signalLampMainOrgName,a.DeviceState " +
                    "from (tb_signal_Lamp se, tb_user ur, tb_org og1, tb_org og2) " +
                    "left join tb_alarm a on (se.AlarmDeviceId=a.DeviceId) " +
                    "where ur.UserId = se.signalLampCreatorId " +
                    "and og1.OrgId = se.signalLampOrgId and og2.OrgId = se.signalLampMainOrgId " +
                    "and se.signalLampStartRoad like '%" + endRoad + "%' and se.signalLampEndRoad like '%" + startRoad + "%' " +
                    "and  se.signalLampCity like '%" + city + "%'";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and se.signalLampMainOrgId=" + orgId + " ";
            else
                sql += "and se.signalLampOrgId = " + orgId + " ";
            if (alarmType.contains("0"))
                sql += " and se.AlarmDeviceId is NULL ";
            else if (alarmType.contains("1"))
                sql += " and se.AlarmDeviceId is not NULL ";
            else
                sql += "";
        }
        sql += "order by concat(signalLampInstallTime, signalLampId) desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalLampDto> getSignalLampById(Integer signalLampId) throws Exception {
        String sql = "select se.* ,o1.orgName as signalLampOrgName,o2.OrgName as signalLampMainOrgName," +
                " u.userName as signalLampCreatorName from tb_signal_Lamp se,tb_org o1,tb_user u,tb_org o2 " +
                "where o1.orgId=se.signalLampOrgId and o2.orgId=se.signalLampMainOrgId and u.UserId=se.signalLampCreatorId" +
                " and se.signalLampId=" + signalLampId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.getResultList();
    }

    public List<String> getCity4SignalLamp(Integer actorId, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "select DISTINCT SignalLampCity from tb_signal_Lamp ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " where SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " where SignalLampOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();
    }

    public List<SignalLampDto> getSignalLamp4Map4App(Integer orgId,
                                                     String actorIds,
                                                     double neLongitude,
                                                     double neLatitude,
                                                     double swLongitude,
                                                     double swLatitude) throws Exception {
        String sql = "select * from tb_signal_Lamp ";
        sql += "where signalLampIsComplete = 1 " +
                "and signalLampLongitude < :neLongitude and signalLampLongitude > :swLongitude " +
                "and signalLampLatitude < :neLatitude and signalLampLatitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and signalLampMainOrgId=" + orgId + " ";
        else
            sql += "and signalLampOrgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.getResultList();
    }

    public List<String> getSectionByStartRoad4App(String roadName, String city, Integer actorId, Integer orgId, Integer mCount, Integer mSize) throws Exception {
        String sql = "SELECT sm.SignalLampStartRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm\n" +
                "WHERE sm.SignalLampEndRoad LIKE '%" + roadName + "%' and sm.SignalLampCity like '%" + city + "%'  ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        sql += " UNION SELECT sm.SignalLampEndRoad AS signalLampStartRoad\n" +
                "FROM tb_signal_Lamp sm\n" +
                "WHERE sm.SignalLampStartRoad LIKE '%" + roadName + "%' and sm.SignalLampCity like '%" + city + "%' ";
        if (actorId != 1) {
            if (actorId == 7)
                sql += " and sm.SignalLampMainOrgId=" + orgId + " ";
            else
                sql += " and sm.SignalLampOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return query.setFirstResult((mCount - 1) * mSize).setMaxResults(mSize).getResultList();

    }

    public List<SignalLampFaultDto> getLampCoordinate4Map(Integer orgId, Integer actorId) throws Exception {
        String sql = "SELECT sl.SignalLampId,sl.SignalLampLongitude,sl.SignalLampLatitude,sl.SignalLampPhoto\n" +
                "FROM tb_signal_lamp sl where sl.SignalLampIsComplete=1 ";
        if (actorId != 1) {
            sql += "  and sl.SignalLampOrgId=" + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        return query.getResultList();
    }

    public List<SignalLampFaultDto> getLampFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String signalLampStartRoad, String signalLampEndRoad) throws Exception {
        String sql = "SELECT sl.SignalLampId,sl.SignalLampPhoto,sl.SignalLampProvince,sl.SignalLampCity,sl.SignalLampArea," +
                "sl.SignalLampRegionId,o.OrgName AS lampOrgName,u.UserName AS signalLampCreatorName," +
                "sl.SignalLampInstallTime\n" +
                "FROM ((tb_signal_lamp sl  JOIN tb_org o ON o.OrgId=sl.SignalLampOrgId)\n" +
                "JOIN tb_user u ON u.UserId=sl.SignalLampCreatorId)\n" +
                "WHERE sl.signalLampStartRoad LIKE '%" + signalLampStartRoad + "%' AND sl.signalLampEndRoad LIKE '%" + signalLampEndRoad + "%'";
        if (actorId != 1) {
            sql += " and sl.signalLampOrgId=" + orgId + "";
        }
        if (signalLampStartRoad != "" || signalLampEndRoad != "") {
            sql += " UNION SELECT sl.SignalLampId,sl.SignalLampPhoto,sl.SignalLampProvince,sl.SignalLampCity,sl.SignalLampArea," +
                    "sl.SignalLampRegionId,o.OrgName AS lampOrgName,u.UserName AS signalLampCreatorName," +
                    "sl.SignalLampInstallTime\n" +
                    "FROM ((tb_signal_lamp sl  JOIN tb_org o ON o.OrgId=sl.SignalLampOrgId)\n" +
                    "JOIN tb_user u ON u.UserId=sl.SignalLampCreatorId)\n" +
                    "WHERE sl.signalLampStartRoad LIKE '%" + signalLampEndRoad + "%' AND sl.signalLampEndRoad LIKE '%" + signalLampStartRoad + "%'";
        }
        if (actorId != 1) {
            sql += " and sl.signalLampOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        List<SignalLampFaultDto> signalLampFaultDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signalLampFaultDtos;
    }

    public List<SignalLampFaultDto> getLampEnergyByXY(String longitude, String latitude) throws Exception {
        String sql = "select sl.signalLampId,sl.signalLampLongitude," +
                "sl.signalLampLatitude,sl.signalLampProvince,sl.signalLampCity,sl.signalLampArea,sl.signalLampStartRoad," +
                "sl.signalLampEndRoad,sl.signalLampPhoto,sl.signalLampType,sl.signalLampBrand,sl.signalLampInstallOrg," +
                "sl.signalLampCreatorId,sl.signalLampMainOrgId, sl.signalLampOrgId " +
                "from tb_signal_lamp sl where sl.signalLampLongitude=" + longitude + "  and sl.signalLampLatitude=" + latitude + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        return query.getResultList();
    }

    public List<SignalLampDto> getDeviceInfo(Integer deviceId) throws Exception {
        String sql = "select sl.signalLampId,sl.SignalLampProvince,sl.SignalLampCity,sl.SignalLampArea,sl.SignalLampStartRoad,sl.SignalLampEndRoad,sl.SignalLampPhoto," +
                "sl.SignalLampOrgId,sl.SignalLampMainOrgId,sl.AlarmDeviceId,o1.OrgName as signalLampOrgName,o2.OrgName as signalLampMainOrgName,sl.ProjectNumber," +
                "sl.SignalLampRegionId,sl.SignalLampLongitude,sl.SignalLampLatitude,sl.SignalLampType,sl.SignalLampBrand,sl.SignalLampInstallOrg,sl.SignalLampInstallTime," +
                "sl.SignalLampPeriod,sl.SignalLampServiceLife,sl.SignalLampRemark,sl.SignalLampCreatorId,sl.SignalLampModifierId,sl.SignalLampIsComplete,sl.SignalLampADeviceId," +
                "sl.AlarmCount " +
                "from tb_signal_lamp sl,tb_org o1,tb_org o2 where sl.SignalLampOrgId = o1.OrgId and sl.SignalLampMainOrgId = o2.OrgId and sl.SignalLampId = " + deviceId + " ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        return query.getResultList();
    }
}
